package com.lyq.demo.excel.controller;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lyq
 * @time 2019/1/2 21:32
 */
@RestController
@Slf4j
public class DemoController {


    @RequestMapping("/upload")
    public Object upload(@RequestParam("file") MultipartFile file) throws Exception {
        String filename = file.getOriginalFilename();
        log.info("filename={}",filename);
        Workbook wb = null;
        //获取输入流
        InputStream is = file.getInputStream();
        if(isExcel2007(filename)){
            wb = new XSSFWorkbook(is);
        }else if(isExcel2003(filename)){
            wb = new HSSFWorkbook(is);
        }else{
            throw new Exception("不是excel文件！");
        }
        //解析文件
        List<String> list = new ArrayList<>();
        Sheet sheet = wb.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for(int i=0;i<lastRowNum;i++){
            Row row = sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            StringBuffer sb =new StringBuffer();
            for(int j=0;j<lastCellNum;j++){
                Cell cell = row.getCell(j);
                if(cell == null){
                    continue;
                }
                CellType cellTypeEnum = cell.getCellTypeEnum();
                switch(cellTypeEnum){
                    case STRING:
                        sb.append(cell.getStringCellValue());
                        break;
                    case NUMERIC:
                        sb.append(cell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        sb.append(cell.getBooleanCellValue());
                        break;
                }
            }
            list.add(sb.toString());
        }

        wb.close();
        is.close();

        return list;
    }

    @RequestMapping("/download")
    public String download() throws Exception{
        XSSFWorkbook wb=new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Sheet1");
        XSSFRow row0=sheet.createRow(0);
        XSSFCell cell0=row0.createCell(0);
        cell0.setCellValue("序号");
        FileOutputStream out = new FileOutputStream("d:/a.xlsx");
        wb.write(out);
        wb.close();
        return "success";
    }

    @GetMapping("/hello")
    public String hello(){
        return "hello";
    }

    public boolean isExcel2003(String filePath){
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public boolean isExcel2007(String filePath){
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}
